﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Js_Study.Excel_Table
{
    public partial class Excel_V2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void load_excel_Click(object sender, EventArgs e)
        {
            DataSet ds = LoadDataFromExcel(Server.MapPath("citiinfo.xlsx"));
            DataTable dt = ds.Tables[0];
            string cells = "ID,CONTINENT,PROVINCE,CITY,CODE,CREATE_TIME,OP_TIME";
            string val = "[";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                val += "{success:";
                if (i % 5 == 0)
                {
                    val += "1,data:[";
                }

                else
                {
                    val += "0,data:[";
                }
                val += "'" + dt.Rows[i]["ID"].ToString() + "',";
                val += "'" + dt.Rows[i]["CONTINENT"].ToString() + "',";
                val += "'" + dt.Rows[i]["PROVINCE"].ToString() + "',";
                val += "'" + dt.Rows[i]["CITY"].ToString() + "',";
                val += "'" + dt.Rows[i]["CODE"].ToString() + "',";
                val += "'" + dt.Rows[i]["CREATE_TIME"].ToString() + "',";
                val += "'" + dt.Rows[i]["OP_TIME"].ToString() + "',";
                val = val.Substring(0, val.Length - 1);
                val += "]},";
            }
            val = val.Substring(0, val.Length - 1);
            val += "]";
            hid.Value = cells + "|" + val;

            span_status.InnerText = "加载完毕!";
        }
        public DataSet LoadDataFromExcel(string filePath)
        {
            try
            {
                string strConn;
                strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", filePath);
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "SELECT * FROM [citiinfo$]";//可是更改Sheet名称，比如sheet2，等等  

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, "citiinfo");
                OleConn.Close();
                return OleDsExcle;
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                return null;
            }
        }
    }
}